﻿CREATE TABLE Foods
(
	IdFood INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	FoodName VARCHAR(100),
	Calories DECIMAL(6,2),
	Carbohydrate DECIMAL(6,2),
	Protein DECIMAL(6,2),
	Fat DECIMAL(6,2),
	Cholesterol DECIMAL(6,2),
	Sugar DECIMAL(6,2),
	VitaminA DECIMAL(6,2),
	VitamnC DECIMAL(6,2),
	Calcium DECIMAL(6,2),
	Iron DECIMAL(6,2)
)

CREATE TABLE FoodEntry
(
	IdFoodEntry INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	IdFood INT NOT NULL,
	Meal VARCHAR(20),
	NumberOfUnits DECIMAL(6,2),
	EntryDate DATE,
	FOREIGN KEY (IdFood) REFERENCES Foods(IdFood)
)
GO

CREATE VIEW FoodEntryView AS
SELECT fe.EntryDate, fe.IdFood, fe.IdFoodEntry, fe.Meal, fe.NumberOfUnits, f.Calcium, f.Calories, f.Carbohydrate, f.Cholesterol, f.Fat, f.FoodName, f.Iron, f.Protein, f.Sugar, f.VitaminA, f.VitaminC
FROM FoodEntry fe
JOIN Foods f ON f.IdFood = fe.IdFood

